//201902104030_何琛选
package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Department;
import cn.edu.sdjzu.xg.bysj.domain.School;
import cn.edu.sdjzu.xg.bysj.service.SchoolService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class DepartmentDao {
    private static Collection<Department> departments;
    private static DepartmentDao departmentDao = null;

    private DepartmentDao() {
    }

    public static DepartmentDao getInstance() {
        if (departmentDao == null){
            departmentDao = new DepartmentDao();
        }
        return departmentDao;
    }

    public int count(Connection connection) throws SQLException{
        return CommonDao.getInstance().count(connection,"department");
    }

    public Integer countAll(School school,Connection connection) throws SQLException{
        //Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String sql_count_all = "select COUNT(id) as cnt_by_school_id from department WHERE school_id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt_count_all= connection.prepareStatement(sql_count_all);
        //为预编译语句赋值
        pstmt_count_all.setInt(1,school.getId());

        ResultSet resultSet_count_all = pstmt_count_all.executeQuery();

        resultSet_count_all.next();

        int count = resultSet_count_all.getInt("cnt_by_school_id");

        JdbcHelper.close(resultSet_count_all, pstmt_count_all, connection);

        return count;
    }

    public Collection<Department> findAll(
            Connection connection, List<Condition> conditionList_json_str, Pagination pagination)
            throws SQLException {
        //创建Department类型集合类对象，HashSet不能保证结果中元素的顺序
        Collection<Department> departments = new HashSet<Department>();

        int totalNum = DepartmentDao.getInstance().count(connection);
        //创建查询departmentd的主语句
        StringBuilder department_sql = new StringBuilder("select * from department");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList_json_str != null) {
            String whereClause = Condition.toWhereClause(conditionList_json_str);
            department_sql.append(whereClause);
        }
        //如果参数不为空，为SQL语句添加LIMIT子句
        if (pagination != null){
            department_sql.append(pagination.toLimitClause(totalNum) + " ");
        }
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(department_sql.toString());
        //获得Department对象的结果集
        ResultSet resultSet = pstmt.executeQuery();

        while (resultSet.next()) {
            //根据id查找结果创建Department对象
            Department department = new Department(
                    resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks"),
                    SchoolService.getInstance().find(resultSet.getInt("school_id"))
            );
            departments.add(department);
        }
        //关闭对象
        JdbcHelper.close(resultSet, pstmt);
        return departments;
    }

    public Department find(Integer id,Connection connection) throws SQLException {
        Department desiredDepartment = null;
        //Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String sql_select_id = "select * from department where id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        //为预编译参数赋值
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        if (resultSet_select_id.next()){
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String remarks = resultSet_select_id.getString("remarks");
            int schoolId = resultSet_select_id.getInt("school_id");

            School school = SchoolService.getInstance().find(schoolId);

            desiredDepartment = new Department(id,description,no,remarks,school);
        }

        JdbcHelper.close(resultSet_select_id,pstmt_select_id,connection);

        return  desiredDepartment;
    }

    public boolean update(Department department,Connection connection) throws SQLException {
        //Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String sql_update =
                "UPDATE department set description=? ,no=? , remarks=? ,school_id = ? where id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt_update = JdbcHelper.getConn().prepareStatement(sql_update);

        pstmt_update.setString(1,department.getDescription());
        pstmt_update.setString(2,department.getNo());
        pstmt_update.setString(3,department.getRemarks());
        pstmt_update.setInt(4,department.getSchool().getId());
        pstmt_update.setInt(5,department.getId());

        int rowAffected = pstmt_update.executeUpdate();

        JdbcHelper.close(pstmt_update,connection);
        return rowAffected>0;
    }

    public boolean add(Department department,Connection connection) throws SQLException {
        //Connection connection = JdbcHelper.getConn();
        String sql_insert = "INSERT INTO Department(description,no,remarks,school_id) VALUES"
                + "(?,?,?,?)";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = JdbcHelper.getConn().prepareStatement(sql_insert);

        pstmt.setString(1,department.getDescription());
        pstmt.setString(2,department.getNo());
        pstmt.setString(3,department.getRemarks());
        pstmt.setInt(4,department.getSchool().getId());

        int rowAffected = pstmt.executeUpdate();

        JdbcHelper.close(pstmt,connection);
        return rowAffected>0;
    }

    public boolean delete(Department department,Connection connection) throws SQLException{
        String deleteDepartment_sql = "delete from department where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteDepartment_sql);
        //为预编译参数赋值
        pstmt.setInt(1, department.getId());
        JdbcHelper.close(null,pstmt);
        //根据id删除Department 并判断是否删除成功
        return pstmt.executeUpdate()>0;
    }
}